package com.gz.common;

import java.util.List;

import com.gz.common.model.Aftersale;
import com.gz.common.model.OrderProduct;
import com.gz.common.model.Product;

public class AftersaleXService {

	private static AftersaleXService service;

	private AftersaleXService() {

	}

	public static AftersaleXService getService() {
		if (service == null) {
			service = new AftersaleXService();
		}
		return service;
	}

	/**
	 * 根据user或者时间区间查询申请售后信息
	 */
	public List<Aftersale> findAftersaleByUserOrtime(String mobile,String createtime,String endtime) {
		StringBuffer sb = new StringBuffer();
		sb.append("select tol.orderSN,u.username,a.aftercontenx,a.aftertype,u.mobile,a.createtime from Aftersale as a\r\n" + 
				"inner join tb_user as u\r\n" + 
				"ON(a.userid=u.id)\r\n"
				+ "inner join tb_order_product as top\r\n" + 
				"ON(top.id=a.productid)\r\n" + 
				"inner join tb_order as tol\r\n" + 
				"ON(top.order_id=tol.id) " + 
				"where 1=1");
		if(mobile!=null&&!mobile.equals("")) {
			sb.append(" and u.mobile like '%?%'");
			return Aftersale.dao.find(sb.toString(),mobile);
		}
		if(createtime!=null&&!createtime.equals("")&&endtime!=null&&!endtime.equals("")) {
			sb.append(" and ? < a.createtime and a.createtime < ?");
			return Aftersale.dao.find(sb.toString(),createtime,endtime);
		}
		return Aftersale.dao.find(sb.toString());
	}
	
	/**
	 * 根据id查询售后对象
	 */
	public Aftersale findAftersaleById(Integer afterid) {
		return Aftersale.dao.findById(afterid);
	}
	
	/**
	 *	根据用户和时间区间查询售出所有商品的成本总金额
	 * @param xiao
	 * @param da
	 * @param userName
	 * @return
	 */
	public List<Product> findProductByUserId(String userName,String xiao,String da){
		StringBuffer sb=new StringBuffer("select pro.`name`,u.username,u.mobile,\r\n" + 
				"\r\n" + 
				"(SELECT count(productid) from aftersale as af \r\n" + 
				" WHERE af.productid=top.id and af.aftertype=1 and af.confirm=1) as tuihouliang,\r\n" + 
				"\r\n" + 
				"count(*)-(SELECT count(productid) from aftersale as af \r\n" + 
				" WHERE af.productid=top.id and af.aftertype=1 and af.confirm=1) as xiaoshouliang,\r\n" + 
				"\r\n" + 
				"((count(*)-(SELECT count(productid) from aftersale as af \r\n" + 
				" WHERE af.productid=top.id and af.aftertype=1 and af.confirm=1))*pro.marketPrice) as zongjine,tbo.createTime \r\n" + 
				"\r\n" + 
				"from tb_product as pro\r\n" + 
				"inner join tb_order_product as top \r\n" + 
				"ON(pro.id=top.product_id)\r\n" + 
				"inner join tb_order as tbo\r\n" + 
				"ON(top.order_id=tbo.id)\r\n" + 
				"inner join tb_user as u\r\n" + 
				"ON(pro.userId=u.id)\r\n" + 
				"where 1=1 and tbo.order_status=4");
		if(userName!=null&&!userName.equals("")) {
			sb.append(" and u.username=?");
			if(xiao!=null&&!xiao.equals("")&&da!=null&&!da.equals("")) {
				sb.append(" and ? < tbo.createTime and tbo.createTime < ? GROUP BY pro.id");
				return Product.dao.find(sb.toString(),xiao,da);
			}
			sb.append(" GROUP BY pro.id");
			return Product.dao.find(sb.toString(),userName);
		}
		sb.append(" GROUP BY pro.id");
		return Product.dao.find(sb.toString());
	}
	
	/**
	 * 查询所有用户商品销售量和成本总金额
	 * @param xiao
	 * @param da
	 * @return
	 */
	public List<OrderProduct> findOrderProductAll(String xiao,String da){
		StringBuffer sb=new StringBuffer();
		sb.append("select u.username,sum(marketPrice) as zongjine,count(1) as zongxiaoliang from tb_order_product as o left join tb_product as p on o.product_id=p.id\r\n" + 
				"left join tb_user as u on u.id=p.userId left join tb_order as orb on orb.id=o.order_id \r\n" + 
				"where orb.order_status=4");
		if(xiao!=null&&!xiao.equals("")&&da!=null&&!da.equals("")) {
			sb.append(" and  ? < orb.createTime && orb.createTime < ? GROUP by  p.userId");
			return OrderProduct.dao.find(sb.toString(),xiao,da);
		}
		sb.append(" GROUP by  p.userId");
		return OrderProduct.dao.find(sb.toString());
	}
}
